package Dao;

import Bean.Audit;
import Bean.User;
import Utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 张
 * Date: 2025-02-28
 * Time: 18:53
 */
public class AuditDao {
    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet resultSet = null;

    //1.新增改签申请
    public int insert(Audit audit) {
        // 显式指定插入字段（排除有默认值的字段）
        String sql = "INSERT INTO ticket_change_audit " +
                "(audit_id, ticket_id, user_id, old_flight_id, new_flight_id, " +
                "requested_seat, auditor_id) " +  // 省略 apply_time 和 audit_status
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            // 1. 生成主键（如果未由调用方提供）
            if (audit.getAuditId() == null || audit.getAuditId().isEmpty()) {
                audit.setAuditId(UUID.randomUUID().toString());
            }
            // 2. 设置参数
            statement.setString(1, audit.getAuditId());
            statement.setString(2, audit.getTicketId());
            statement.setString(3, audit.getUserId());
            statement.setString(4, audit.getOldFlightId());
            statement.setString(5, audit.getNewFlightId());
            statement.setString(6, audit.getRequestedSeat());
            statement.setString(7, audit.getAuditorId());
            // 3. 执行插入
            int ret = statement.executeUpdate();
            return ret;

        } catch (SQLException e) {
            // 实际项目中应记录日志并抛出业务异常
            e.printStackTrace();
            return 0;
        } finally {
            DBUtil.close(connection,statement,null);
        }
    }


    //2.查询所有申请数据(管理员使用)
    public List<Audit> select(int pageNo,int pageSize) {
        List<Audit> array = new ArrayList<>();
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "select * from ticket_change_audit where audit_status = '待审核' LIMIT ?,?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,(pageNo-1)*pageSize);
            statement.setInt(2,pageSize);
            resultSet = statement.executeQuery();
            if(resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Audit audit = new Audit();
                    audit.setAuditId(resultSet.getString("audit_id"));
                    audit.setTicketId(resultSet.getString("ticket_id"));
                    audit.setUserId(resultSet.getString("user_id"));
                    audit.setOldFlightId(resultSet.getString("old_flight_id"));
                    audit.setNewFlightId(resultSet.getString("new_flight_id"));
                    audit.setRequestedSeat(resultSet.getString("requested_seat"));
                    audit.setApplyTime(resultSet.getObject("apply_time", LocalDateTime.class));
                    audit.setAuditorId(resultSet.getString("auditor_id"));
                    audit.setAuditStatus(Audit.AuditStatus.valueOf(resultSet.getString("audit_status")));
                    array.add(audit);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }

    //3.删除申请列表 (审核完成之和即可删除)
    public boolean delete(String AuditId) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装sql语句
            String sql = "delete from ticket_change_audit where audit_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,AuditId);
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }

    //4.批准申请
    public boolean ratify(Audit audit) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装sql语句
            String sql = "update ticket_change_audit SET audit_status = ? where audit_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,audit.getAuditStatus().name());
            statement.setString(2,audit.getAuditId());
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public int size(String userId) {
        int size = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS ticket_count FROM ticket_change_audit where user_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,userId);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("ticket_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    public List<Audit> selectByUserId(int pageNo, int pageSize, String userId) {
        List<Audit> array = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "select * from ticket_change_audit  where user_id = ? LIMIT ?,?";
            statement = connection.prepareStatement(sql);
            statement.setInt(2,(pageNo-1)*pageSize);
            statement.setInt(3,pageSize);
            statement.setString(1,userId);
            resultSet = statement.executeQuery();
            if(resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Audit audit = new Audit();
                    audit.setAuditId(resultSet.getString("audit_id"));
                    audit.setOldFlightId(resultSet.getString("old_flight_id"));
                    audit.setNewFlightId(resultSet.getString("new_flight_id"));
                    audit.setRequestedSeat(resultSet.getString("requested_seat"));
                    audit.setApplyTime(resultSet.getObject("apply_time", LocalDateTime.class));
                    audit.setAuditStatus(Audit.AuditStatus.valueOf(resultSet.getString("audit_status")));
                    array.add(audit);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }

    public int size () {
        int size = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS ticket_count FROM ticket_change_audit";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("ticket_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    public boolean update(Audit audit) {
        try{
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装sql语句
            connection = DBUtil.getConnection();
            String sql="UPDATE ticket_change_audit SET audit_status = ? , ticket_id = ? where audit_id = ?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,audit.getAuditStatus().name());
            statement.setString(2,audit.getTicketId());
            statement.setString(3,audit.getAuditId());
            int ret = statement.executeUpdate();
            //3.执行sql
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }
}
